﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Dapper;

namespace EasyInvoice.SqlCEDAL
{
    public class Invoices:IInvoices
    {
        public EasyInvoice.Model.Invoice GetById(long id)
        {
            try
            {
                using (System.Data.SqlServerCe.SqlCeConnection sqlConnection = new System.Data.SqlServerCe.SqlCeConnection(Properties.Settings.Default.ConnectionString))
                {
                    sqlConnection.Open();
                    var p = new DynamicParameters();
                    p.Add("@Id", id, System.Data.DbType.Int32, direction: ParameterDirection.Input);
                    string sql = string.Empty;
                    sql = string.Format("SELECT * FROM [Invoices] WHERE [Id] = @Id");

                    IEnumerable<EasyInvoice.Model.Invoice> invoices = sqlConnection.Query<EasyInvoice.Model.Invoice>(sql, p, commandType: CommandType.Text);

                    sqlConnection.Close();
                    if (invoices == null)
                    {
                        return null;
                    }
                    else
                    {
                        EasyInvoice.Model.Invoice returnInvoice = invoices.FirstOrDefault<EasyInvoice.Model.Invoice>();
                        return returnInvoice;
                    }
                }
            }
            catch (Exception)
            {
                throw;
            }
        }

        public IEnumerable<EasyInvoice.Model.Invoice> GetAll()
        {
            try
            {
                using (System.Data.SqlServerCe.SqlCeConnection sqlConnection = new System.Data.SqlServerCe.SqlCeConnection(Properties.Settings.Default.ConnectionString))
                {
                    sqlConnection.Open();
                    string sql = string.Empty;
                    sql = string.Format("SELECT * FROM [Invoices] order by [Date] desc");

                    IEnumerable<EasyInvoice.Model.Invoice> invoices = sqlConnection.Query<EasyInvoice.Model.Invoice>(sql, commandTimeout: 0, commandType: CommandType.Text);

                    sqlConnection.Close();

                    return invoices;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public long Save(EasyInvoice.Model.Invoice invoice)
        {
            try
            {
                using (System.Data.SqlServerCe.SqlCeConnection sqlConnection = new System.Data.SqlServerCe.SqlCeConnection(Properties.Settings.Default.ConnectionString))
                {

                    sqlConnection.Open();

                    var p = new DynamicParameters();
                    p.Add("@Id", invoice.Id, System.Data.DbType.Int32, direction: ParameterDirection.Input);
                    p.Add("@Number", invoice.Number, System.Data.DbType.String, direction: ParameterDirection.Input);
                    p.Add("@Date", invoice.Date, System.Data.DbType.DateTime, direction: ParameterDirection.Input);
                    p.Add("@ClientId", invoice.ClientId, System.Data.DbType.Int32, direction: ParameterDirection.Input);
                    p.Add("@SellerId", invoice.SellerId, System.Data.DbType.Int32, direction: ParameterDirection.Input);
                    string sql = string.Empty;
                    if (invoice.Id == 0)
                        sql = string.Format("INSERT INTO [Invoices] ([Number],[Date],[ClientId],[SellerId]) VALUES (@Number,@Date,@ClientId,@SellerId);");
                    else
                        sql = string.Format("UPDATE [Invoices] SET [Number] = @Number, [Date] = @Date, [ClientId] = @ClientId, [SellerId] = @SellerId WHERE [Id] = @Id;");

                    sqlConnection.Execute(sql, p, commandType: CommandType.Text);

                    if (invoice.Id == 0)
                    {
                        sql = string.Format("SELECT * FROM [Invoices] WHERE [Id] = @@IDENTITY");
                        IEnumerable<EasyInvoice.Model.Invoice> invoices = sqlConnection.Query<EasyInvoice.Model.Invoice>(sql, commandType: CommandType.Text);
                        invoice.Id = invoices.First().Id;
                    }
                    sqlConnection.Close();

                    return invoice.Id;
                }

            }
            catch (Exception)
            {
                throw;
            }
        }

        public EasyInvoice.Model.Invoice Delete(EasyInvoice.Model.Invoice invoice)
        {
            try
            {
                using (System.Data.SqlServerCe.SqlCeConnection sqlConnection = new System.Data.SqlServerCe.SqlCeConnection(Properties.Settings.Default.ConnectionString))
                {
                    sqlConnection.Open();
                    var p = new DynamicParameters();
                    p.Add("@Id", invoice.Id, System.Data.DbType.Int32, direction: ParameterDirection.Input);
                    string sql = string.Format("DELETE FROM [Invoices] WHERE [Id] = @Id");
                    sqlConnection.Execute(sql, p, commandType: CommandType.Text);
                    sqlConnection.Close();
                    return invoice;
                }
            }
            catch (Exception)
            {
                throw;
            }
           
        }
    }
}
